IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[InjectPayrollSnapshot]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[InjectPayrollSnapshot]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
/***************************************************************************
* Name:			InjectPayrollSnapshot
* Purpose:		Injects Payroll Snapshot data to compare for retro-salary
*				encumbrances.
* 
select p.* from #TargetEmployeeJobs t 
inner join dbo.PerEmployee_Job_Map j on j.id = t.PerEmployeeJobMapId
inner join dbo.AdmInternalPerson ip on ip.id = j.AdmInternalPersonId
inner join dbo.admperson p on p.id = ip.admpersonid

select * from perSnapshotSalaryAdjustment
select * from perSnapshotSalaryDistribution
select * from perSnapshotSalaryDistributionsplit

* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
Create Procedure dbo.[InjectPayrollSnapshot]
AS
Begin

set nocount on
DECLARE @journalStartDate 		DATETIME

DECLARE @fiscalYearStartDate	DATETIME
DECLARE @fiscalYearDays			MONEY
DECLARE @fiscalYearEndDate	 	DATETIME
DECLARE @yearStartDate 			VARCHAR(6)
DECLARE @yearEndDate 			VARCHAR(6)
DECLARE @fiscalYear 			VARCHAR(4)
declare @lastGLCloseDate		datetime
declare @NotManaged				int
declare @SnapshotDate			datetime
declare @todaysDate				datetime

set @todaysDate = getdate()
set @NotManaged = 3
    
/*------------------------------------------------------------------------------------
	Get the last GL close date!  This is always the month ending date when we close
	the GL. 
-------------------------------------------------------------------------------------*/
IF OBJECT_ID('tempdb..#RefreshDateTable') IS NOT NULL drop  table #RefreshDateTable
Create table #RefreshDateTable(glPostAsOfDate datetime, salaryAsOfDate datetime, poapAsOfDate datetime, dummydate datetime)
insert into #RefreshDateTable exec getrefreshdates
select	@lastGLCloseDate = glPostAsOfDate from #RefreshDateTable

/*------------------------------------------------------------------------------------
	Get the actual date when the last gl update processed. THIS IS NOT THE CLOSE DATE!
	This date usually will be 11 business days after the close date.  For Example:
    Our last close was for 6/30/2009, but it actually processed on 7/14/2009.  We
	merely get the max last update date from the journal transaction table.
declare @DateLastGLUpdate datetime
select @DateLastGLUpdate = max(datelastupdated) from dbo.finjournaltransaction_src (Nolock)
-------------------------------------------------------------------------------------*/
	
SET @journalStartDate = DATEADD(DD, 1, @lastGLCloseDate)

SET @yearStartDate = '10/01/'	
SET @yearEndDate = '09/30/'	
SET @fiscalYear = CAST(DATEPART(YEAR, @journalStartDate) AS VARCHAR)
IF DATEPART(MONTH, @journalStartDate) <= 9 
	BEGIN
	SET @fiscalYearStartDate = @yearStartDate + cast((cast(@fiscalYear as int)-1) as varchar(4)) 
	SET @fiscalYearEndDate = @yearEndDate + @fiscalYear
	END
ELSE
	BEGIN
	SET @fiscalYearStartDate = @yearStartDate + @fiscalYear
	SET @fiscalYear = @fiscalYear + 1
	SET @fiscalYearEndDate = @yearEndDate + @fiscalYear 
	END

SET @fiscalYearDays = convert(money,IsNull(datediff(d,@fiscalYearStartDate,@fiscalYearEndDate)+1,365))
/*-------------------------------------------------------
Compute the snapshot date as the end of month date after
the last GL feed run date.
-------------------------------------------------------*/
set @SnapshotDate = dateadd(day,-1,dateadd(month,1,dateadd(day,1,@lastGLCloseDate)))

/*-------------------------------------------------------
Delete any old snapshots with dates not equal to the 
current snapshot date.
-------------------------------------------------------*/
Delete from dbo.perSnapshotsalaryadjustment where SnapshotDate <> @SnapshotDate
Delete from dbo.PerSnapshotSalaryDistribution where SnapshotDate <> @SnapshotDate
Delete from dbo.PerSnapshotSalaryDistributionSplit where SnapshotDate <> @SnapshotDate

/*-------------------------------------------------------
Detect personnel that need to have a snapshot taken.
The criteria is: managed HR department, Salaried Monthly
employee, change date since the last run date and no
snapshot for the current fiscal period.
-------------------------------------------------------*/
if object_id('tempdb..#TargetEmployeeJobs') is not null drop table #TargetEmployeeJobs
select j.id as 'PerEmployeeJobMapId'
into #TargetEmployeeJobs
from dbo.PerHRDepartment h
inner join dbo.PerEmployee_Job_Map j on j.perHrDepartmentId = h.id
inner join dbo.perJobType jt on jt.id = j.perJobTypeId
inner join dbo.perPayType pt on pt.id = jt.PerPayTypeId
inner join dbo.perPayFrequencyType pft on pft.id = jt.perPayFrequencyTypeId 
where h.PerHRManagementTypeId <> @NotManaged and j.IsTracked = 1
	and (exists(select 1 from dbo.PerSalaryAdjustment sa 
				where sa.PerEmployeeJobMapId = j.id and sa.ModifiedDate > @lastGLCloseDate 
						and sa.modifiedby <> 'iBridge')
		 or exists(select 1 from dbo.PerSalaryDistribution sd 
					where sd.PerEmployeeJobMapId = j.id and sd.DateLastModified > @lastGLCloseDate 
						and sd.modifiedby <> 'iBridge'))
	and not exists(select 1 from dbo.PerSnapshotSalaryAdjustment ssd 
					where ssd.PerEmployeeJobMapId = j.id and ssd.SnapshotDate = @SnapshotDate)

/*-------------------------------------------------------
Insert into the Snapshot Salary Adjustment table
-------------------------------------------------------*/
insert into dbo.perSnapshotsalaryadjustment (
Id
,PerEmployeeJobMapId
,AnnualRate
,EffectiveDate
,ModifiedBy
,ModifiedDate
,DateCreated
,SnapShotDate
)
Select
sa.Id
,sa.PerEmployeeJobMapId
,sa.AnnualRate
,sa.EffectiveDate
,sa.ModifiedBy
,sa.ModifiedDate
,sa.DateCreated
,@SnapshotDate
 from dbo.persalaryadjustment_src sa
inner join #TargetEmployeeJobs tj on sa.PerEmployeeJobMapId = tj.PerEmployeeJobMapId

/*-------------------------------------------------------
Insert into the Snapshot Salary Distribution table
-------------------------------------------------------*/
INSERT INTO dbo.PerSnapshotSalaryDistribution
           ([ID]
			,[AgrFundId]
           ,[IsExternalFund]
           ,[PerEmployeeJobMapId]
           ,[Description]
           ,[EmployeeId]
           ,[HoursPerWeek]
           ,[AnnualRate]
           ,[PercentEffort]
           ,[EffectiveDate]
           ,[ExpirationDate]
           ,[Comment]
           ,[ModifiedBy]
           ,[DateLastModified]
		   ,[SnapShotDate])
SELECT 
	  sd.Id
      ,sd.[AgrFundId]
      ,sd.[IsExternalFund]
      ,sd.[PerEmployeeJobMapId]
      ,sd.[Description]
      ,sd.[EmployeeId]
      ,sd.[HoursPerWeek]
      ,sd.[AnnualRate]
      ,sd.[PercentEffort]
      ,sd.[EffectiveDate]
      ,sd.[ExpirationDate]
      ,sd.[Comment]
      ,sd.[ModifiedBy]
      ,sd.[DateLastModified]
	  ,@SnapshotDate
  FROM dbo.PerSalaryDistribution_Src sd
  inner join #TargetEmployeeJobs tj on sd.PerEmployeeJobMapId = tj.PerEmployeeJobMapId

/*-------------------------------------------------------
Insert into the Snapshot Split table
-------------------------------------------------------*/
INSERT INTO dbo.PerSnapshotSalaryDistributionSplit
           (ID
		   ,[PerSalaryDistributionId]
           ,[PerSalaryAdjustmentId]
           ,[PercentEffort]
           ,[AnnualRate]
           ,[EffectiveDate]
           ,[ExpirationDate]
		   ,[SnapshotDate])
SELECT spl.Id
      ,drv.[PerSalaryDistributionId]
      ,drv1.[PerSalaryAdjustmentId]
      ,spl.[PercentEffort]
      ,spl.[AnnualRate]
      ,spl.[EffectiveDate]
      ,spl.[ExpirationDate]
	  ,@SnapshotDate
  FROM dbo.PerSalaryDistributionSplit_src spl
INNER JOIN 
	(Select
		sds.Id as PerSalaryDistributionId
		,sds.PerEmployeeJobMapId
     from
		PerSalaryDistribution_src sds
	 inner join #TargetEmployeeJobs tj on sds.PerEmployeeJobMapId = tj.PerEmployeeJobMapId) drv
 ON
	spl.PerSalaryDistributionId = drv.PerSalaryDistributionId
INNER JOIN 
	(Select
		sas.Id as PerSalaryAdjustmentId
     from
		PerSalaryAdjustment_src sas
	 inner join #TargetEmployeeJobs tj on sas.PerEmployeeJobMapId = tj.PerEmployeeJobMapId) drv1
 ON
	spl.PerSalaryAdjustmentId = drv1.PerSalaryAdjustmentId

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
